﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraCharts;
using System.Data.SqlClient;
using Lists;
using GeneralLibs;

namespace BI
{
    public partial class UcDashBoard : UserControl
    {
        private static int _NumberOfReIndex = 0;

        public UcDashBoard()
        {
            InitializeComponent();
        }

        private void UcDashBoard_Load(object sender, EventArgs e)
        {
            if (DesignMode) return;
            DateTime now = GeneralLibs.CommonLib.GetSystemDateTime();
            dtNgayKetThuc.DateTime = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
            numInterval.Value = 7;
            cmbKyBaoCao.SelectedIndex = 17;
            cmbKyBaoCao.SelectedIndex = 18;
        }

                                                                                                                                                                                                                                                                                                                                                                                                        private void cmbKyBaoCao_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                DateTime ngayBatDau = dtNgayBatDau.DateTime;
                DateTime ngayKetThuc = dtNgayBatDau.DateTime;
                DateTime now = GeneralLibs.CommonLib.GetSystemDateTime();
                switch (cmbKyBaoCao.Text)
                {
                    case "Hôm nay":
                        dtNgayBatDau.Enabled = true;
                        dtNgayKetThuc.Enabled = true;
                        ngayBatDau = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
                        ngayKetThuc = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
                        break;
                    case "Tuần gần nhất":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0).AddDays(-7);
                        ngayKetThuc = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
                        break;
                    case "Tháng này":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
                        ngayKetThuc = new DateTime(now.Year, now.Month, System.DateTime.DaysInMonth(now.Year, now.Month), 23, 59, 59);
                        break;
                    case "Tùy chọn":
                        dtNgayBatDau.Enabled = true;
                        dtNgayKetThuc.Enabled = true;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 1, 1);
                        ngayKetThuc = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59);
                        break;
                    case "Cả năm":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 1, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 12, 31, 23, 59, 59);
                        break;
                    case "Quý I":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 1, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 3, 31, 23, 59, 59);
                        break;
                    case "Quý II":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 4, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 6, 30, 23, 59, 59);
                        break;
                    case "Quý III":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 7, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 9, 30, 23, 59, 59);
                        break;
                    case "Quý IV":
                        dtNgayBatDau.Enabled = false;
                        dtNgayKetThuc.Enabled = false;
                        ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, 10, 1);
                        ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, 12, 31, 23, 59, 59);
                        break;
                    default:
                        try
                        {
                            dtNgayBatDau.Enabled = false;
                            dtNgayKetThuc.Enabled = false;
                            string strThang = ((string)cmbKyBaoCao.SelectedItem).Substring(((string)(cmbKyBaoCao.SelectedItem)).LastIndexOf(" "));
                            int thang = int.Parse(strThang);

                            ngayBatDau = new DateTime(GeneralLibs.Config.NamTaiChinh, thang, 1);
                            ngayKetThuc = new DateTime(GeneralLibs.Config.NamTaiChinh, thang,
                                            DateTime.DaysInMonth(GeneralLibs.Config.NamTaiChinh, thang), 23, 59, 59);
                        }
                        catch (SqlException sqlEx)
                        {
                            throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
                        }
                        catch (Exception ex)
                        {
                            if (ex is CustomException) throw ex;
                            else
                            {
                                throw new Exception(ex.Message);
                            }
                        }
                        break;
                }
                dtNgayBatDau.DateTime = ngayBatDau;
                dtNgayKetThuc.DateTime = ngayKetThuc;
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            //Nếu chưa đánh lại lần nào. Đã đánh rồi thì có thể k phải do lỗi này. K nên thực hiện nhiều lần
            if (_NumberOfReIndex < 1) ReIndexDB();

            BasicForms.FrmProgressBar.StartShowProgress();
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
                cmd.ExecuteNonQuery();

                DateTime dtPointer = dtNgayBatDau.DateTime;
                Guid taiKhoanLai = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");

                Guid taiKhoanTienMatID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("TienMat");
                SoDuTaiKhoan soDuTaiKhoanTienMat = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanTienMatID);
                ucDigitalNumberTienMat.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanTienMat.DuNo);

                Guid taiKhoanTienGuiNganHangID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("TienGuiNganHang");
                SoDuTaiKhoan soDuTaiKhoanTienGuiNganHang = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanTienGuiNganHangID);
                ucDigitalNumberTienGuiNganHang.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanTienGuiNganHang.DuNo); 

                Guid taiKhoanHangHoaID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("GiaMuaHangHoa");
                SoDuTaiKhoan soDuTaiKhoanHangHoa = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanHangHoaID);
                if (soDuTaiKhoanHangHoa.DuCo > 0)
                    MessageBox.Show("Cần kiểm tra lại số dư tài khoản thuốc. Hiện tại đang âm " + soDuTaiKhoanHangHoa.DuCo.ToString());
                ucDigitalNumberHangHoa.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanHangHoa.DuNo); 

                Guid taiKhoanNoPhaiThuID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("PhaiThuKhachHang");
                SoDuTaiKhoan soDuTaiKhoanNoPhaiThu = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanNoPhaiThuID);
                ucDigitalNumberPhaiThu.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanNoPhaiThu.DuNo); 

                Guid taiKhoanNoPhaiTraID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("PhaiTraNhaCungCap");
                SoDuTaiKhoan soDuTaiKhoanNoPhaiTra = SoDuTaiKhoan.CalculateFromDB(dtNgayKetThuc.DateTime, taiKhoanNoPhaiTraID);
                ucDigitalNumberPhaiTra.DisplayText = GeneralLibs.CommonLib.Decimal2Money(soDuTaiKhoanNoPhaiTra.DuCo);

                Guid taiKhoanDoanhThuID = (Guid)DanhMucCauHinhTaiKhoanMacDinh.Instance.GetTaiKhoanIDByTinhChat("DoanhThuBanHangVaCungCapDichVu");
                //ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money(TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime));

                decimal tongThu = TaiKhoan.TinhSoPhatSinhNo(cmd, taiKhoanTienMatID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) +
                                TaiKhoan.TinhSoPhatSinhNo(cmd, taiKhoanTienGuiNganHangID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) -
                                TaiKhoan.TinhSoPhatSinhDoiUng(cmd, taiKhoanTienMatID, taiKhoanTienGuiNganHangID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) -
                                TaiKhoan.TinhSoPhatSinhDoiUng(cmd, taiKhoanTienGuiNganHangID, taiKhoanTienMatID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime);
                decimal tongChi = TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanTienMatID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) +
                                TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanTienGuiNganHangID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) -
                                TaiKhoan.TinhSoPhatSinhDoiUng(cmd, taiKhoanTienMatID, taiKhoanTienGuiNganHangID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime) -
                                TaiKhoan.TinhSoPhatSinhDoiUng(cmd, taiKhoanTienGuiNganHangID, taiKhoanTienMatID, dtNgayBatDau.DateTime, dtNgayKetThuc.DateTime);

                ucDigitalNumberTongThu.DisplayText = GeneralLibs.CommonLib.Decimal2Money(tongThu);
                ucDigitalNumberTongChi.DisplayText = GeneralLibs.CommonLib.Decimal2Money(tongChi);
                
                #region tinh lai gop trong ky
                cmd.CommandText = "SELECT SUM(Value5) AS DoanhSoBan, SUM(Value5 - Value3) AS Lai\n" +
                                        "FROM KhoanMucChungTu \n" +
                                        "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                        "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                        "											FROM ChungTu \n" +
                                        "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                        "																	WHERE Ma IN (N'HoaDonBanHang', N'HoaDonBanLe') \n" +
                                        "																	) \n" +
                                        "                                                    AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1\n" +
                                        "											)\n" +
                                        "                   )\n" + 
                                        " AND Value5 IS NOT NULL AND Value3 IS NOT NULL ";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", dtNgayBatDau.DateTime);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", dtNgayKetThuc.DateTime);

                ucDigitalNumberLaiGop.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);//default value
                ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);//default value
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    try
                    {
                        if (dr["Lai"] != DBNull.Value && dr["DoanhSoBan"] != DBNull.Value)
                        {
                            ucDigitalNumberLaiGop.DisplayText = GeneralLibs.CommonLib.Decimal2Money((decimal)dr["Lai"]);
                            ucDigitalNumberDoanhSo.DisplayText = GeneralLibs.CommonLib.Decimal2Money((decimal)dr["DoanhSoBan"]);
                        }
                    }
                    catch
                    {
                    }
                }
                dr.Close();
                #endregion

                #region tinh doanh so mua hang
                cmd.CommandText = "SELECT SUM(SotienNT) DoanhSoMuaHang\n" +
                                    "FROM ChungTu  \n" +
                                    "WHERE LoaiChungTuID = (SELECT ID FROM LoaiChungTu  \n" +
                                    "						WHERE Ma = N'HoaDonMuaHang' \n" +
                                    "						)         \n" +
                                    "       AND SoTienNT IS NOT NULL AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", dtNgayBatDau.DateTime);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", dtNgayKetThuc.DateTime);
                dr = cmd.ExecuteReader();

                ucDigitalNumberMuaHang.DisplayText = GeneralLibs.CommonLib.Decimal2Money(0);//default value
                if (dr.Read())
                {
                    try
                    {
                        if (dr["DoanhSoMuaHang"] != DBNull.Value)
                            ucDigitalNumberMuaHang.DisplayText = GeneralLibs.CommonLib.Decimal2Money((decimal)dr["DoanhSoMuaHang"]);
                    }
                    catch
                    {
                    }
                }
                dr.Close();
                #endregion

                ucTwoSeriesChart.Data.Rows.Clear();
                while (dtPointer <= dtNgayKetThuc.DateTime)
                {
                    DataRow row = ucTwoSeriesChart.Data.NewRow();
                    row["Series1Name"] = string.Format("{0}/{1}/{2}", dtPointer.Day, dtPointer.Month, dtPointer.Year);
                    decimal phatSinh = TaiKhoan.TinhSoPhatSinhCo(cmd, taiKhoanDoanhThuID, dtPointer, dtPointer.AddDays(1));
                    row["Series1Value"] = phatSinh / 1000000;

                    row["Series2Name"] = string.Format("{0}/{1}/{2}", dtPointer.Day, dtPointer.Month, dtPointer.Year);
                    cmd.CommandText = "SELECT SUM(Value5 - Value3) AS Lai\n" +
                                        "FROM KhoanMucChungTu \n" +
                                        "WHERE ChungTuID IN (SELECT ChungTuID FROM ChungTu  \n" +
                                        "					WHERE ChungTuGocID IN (SELECT ChungTuID \n" +
                                        "											FROM ChungTu \n" +
                                        "											WHERE LoaiChungTuID IN (SELECT ID FROM LoaiChungTu \n" +
                                        "																	WHERE Ma IN (N'HoaDonBanHang', N'HoaDonBanLe') \n" +
                                        "																	) \n" +
                                        "                                                   AND @ThoiDiemDau <= Ngay AND Ngay <= @ThoiDiemCuoi AND GhiSo = 1\n" +
                                        "											)\n" +
                                        "                   )";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddWithValue("ThoiDiemDau", dtPointer);
                    cmd.Parameters.AddWithValue("ThoiDiemCuoi", dtPointer.AddDays(1));
                    dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        if (dr["Lai"] is decimal)
                            row["Series2Value"] = (decimal)dr["Lai"] / 1000000;
                        else
                        {
                            object abc = dr["Lai"];
                            string teyp = abc.GetType().ToString();
                            row["Series2Value"] = (decimal)0;
                        }
                    }
                    dr.Close();


                    ucTwoSeriesChart.Data.Rows.Add(row);
                    dtPointer = dtPointer.AddDays((int)numInterval.Value);
                }
                ucTwoSeriesChart.GenChart("Doanh số", "Lãi", ViewType.Bar, "Doanh số theo ngày (đv: triệu VNĐ)");
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
            finally
            {
                con.Close();
            }

            BasicForms.FrmProgressBar.FinishShowProgress();
        }

        private void ReIndexDB()
        {
            _NumberOfReIndex++;
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandTimeout = 1000 * 10;

                cmd.CommandText = string.Format("DECLARE @MyTable VARCHAR(255) \n" +
                                                "DECLARE myCursor CURSOR FOR \n" +
                                                "   SELECT table_name \n" +
                                                "   FROM information_schema.tables \n" +
                                                "   WHERE table_type = 'base table' \n" +
                                                "OPEN myCursor \n" +
                                                "FETCH NEXT FROM myCursor INTO @MyTable \n" +
                                                "WHILE @@FETCH_STATUS = 0 \n" +
                                                "BEGIN \n" +
                                                "   PRINT 'Reindexing Table:  ' + @MyTable \n" +
                                                "   DBCC DBREINDEX(@MyTable, '', 80) \n" +
                                                "   FETCH NEXT FROM myCursor INTO @MyTable \n" +
                                                "END \n" +
                                                "CLOSE myCursor \n" +
                                                "DEALLOCATE myCursor \n" +
                                                "EXEC sp_updatestats \n"
                                                );
                BasicForms.FrmProgressBar.StartShowProgress("Đang đánh lại chỉ mục cho các bảng trong CSDL để tăng tốc độ truy vấn");
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Gặp sự cố khi đánh lại chỉ mục cho các bảng trong CSDL");
            }
            finally
            {
                BasicForms.FrmProgressBar.FinishShowProgress();
                if (con.State == ConnectionState.Open) con.Close();
            }
        }
    }
}
